﻿using System;
using System.Data;
using System.Configuration;

using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;

using System.Data.SqlClient;
using TN5Y;
using StillVoiceCommon;
using Microsoft.ApplicationBlocks.Data;
using TeamTime.Classes;

namespace StillVoiceCommon
{
    public class Security
    {
        public string ConnectionString;
        //for returning debug info 
        public string Test;
        public string SQL = "SELECT * from users where id = " + 0;
        public int UserID;
        private SqlConnection conn;
        private SqlDataAdapter adap;
        //private Functions f = new Functions();

        public Security(string SecDBConnString, int UserID)
        {
            this.initClass(SecDBConnString);
            this.UserID = UserID;
        }
        public Security(string SecDBConnString)
        {
            this.initClass(SecDBConnString);
        }
        public Security(Int32 userID)
        {
            this.initClass(Utilities.DBConnectionString);
            this.UserID = userID;
        }
        public void initClass(string ConnString)
        {
            this.ConnectionString = ConnString;
            adap = new SqlDataAdapter(SQL, conn);
            //conn = New SqlConnection(ConnectionString) 
            //conn.Open() 
            //            sqlDB sqldb = new sqlDB();
            conn = sqlDB.getDBConnExtra(ConnectionString);
            this.Test = this.ConnectionString;
        }

        //wrapper for the GetUser function below which also logs the login 
        // it returns a dataset with the user record, empty if not found 
        public DataSet LoginUser(string LoginCode, string Password)
        {
            DataSet functionReturnValue = null;
            DataTableRow tr = new DataTableRow();
            DataRow r;

            functionReturnValue = new DataSet();
            functionReturnValue = GetUser(LoginCode, Password);
            if (functionReturnValue.Tables[0].Rows.Count > 0)
            {
                //record the login 
                r = functionReturnValue.Tables[0].Rows[0];
                {
                    tr.initClass(ConnectionString, "[Security].LoginHistory", "0", "ID", false, Convert.ToInt32(r["ID"]));
                    tr.Fields["UserID"] = r["ID"];
                    tr.Fields["LoginDate"] = DateTime.Now;
                    tr.update();
                    this.SQL = tr.SQL;
                }
            }
            return functionReturnValue;
        }

        internal DataSet getSecurityID()
        {
            string sql = " select ID,SecurityItem from Security.SecurityItem ";
            return SqlHelper.ExecuteDataset(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql);
        }
        //return the user record given the ID
        public DataSet GetUser(int UserID)
        {
            DataSet functionReturnValue = null;
            SQL = "select * from [Security].[User] ";
            if (UserID > 0)
            {
                SQL += " where UserID = " + UserID.ToString();
            }

            SqlCommand comm = new SqlCommand(SQL, conn);
            functionReturnValue = new DataSet();
            adap.SelectCommand = comm;
            adap.Fill(functionReturnValue);
            return functionReturnValue;
        }
        //return the user record given the login and password 
        public DataSet GetUser(string LoginCode, string Password, int ID)
        {
            DataSet functionReturnValue = null;
            SQL = "select * from [Security].[User] ";
            if (ID > 0)
            {
                SQL += " where UserID = " + ID.ToString();
            }
            else
            {
                SQL += " where LoginCode = '" + LoginCode + "' and Password = '" + Password + "' ";
            }

            SqlCommand comm = new SqlCommand(SQL, conn);
            functionReturnValue = new DataSet();
            adap.SelectCommand = comm;
            adap.Fill(functionReturnValue);
            return functionReturnValue;
        }
        public DataSet GetUser(string LoginCode, string Password)
        {
            DataSet functionReturnValue = null;
            SQL = "select * from [Security].[User] ";
            SQL += " where LoginCode = '" + LoginCode + "' and Password = '" + Password + "' ";

            SqlCommand comm = new SqlCommand(SQL, conn);
            functionReturnValue = new DataSet();
            adap.SelectCommand = comm;
            adap.Fill(functionReturnValue);
            return functionReturnValue;
        }

        public string getUserName(int UserID)
        {
            string functionReturnValue = null;
            System.Data.SqlClient.SqlDataReader dr;
            SQL = "select * from [Security].[User] where ID = " + UserID;
            SqlCommand cmd = new SqlCommand(SQL, conn);
            dr = cmd.ExecuteReader();
            if (dr.Read())
                functionReturnValue = dr["Firstname"] + " " + dr["LastName"];
            dr.Close();
            return functionReturnValue;
        }

        public DataSet GetUserMenuItems(int UserID)
        {
            DataSet functionReturnValue = null;
            SQL = "select distinct mi.* ";
            SQL += "from menuitems mi ";
            SQL += "inner join dbo.ViewUserSecurityItems vusi ";
            SQL += "on (mi.ID = vusi.ID or vusi.UserGroup = 'All Access') ";
            SQL += "where vusi.userid = 1 ";
            SQL += "and URL is not null ";
            SQL += "order by mi.[order]";
            SqlCommand comm = new SqlCommand(SQL, conn);
            functionReturnValue = new DataSet();
            adap.SelectCommand = comm;
            adap.Fill(functionReturnValue);
            return functionReturnValue;
        }

        public bool CanRead(int UserID, string SecurityItem)
        {
            return (GetAccessLevel(UserID, SecurityItem) >= 1);
        }
        // 
        public bool CanAdd(int UserID, string SecurityItem)
        {
            return (GetAccessLevel(UserID, SecurityItem) >= 2);
        }
        // 
        public bool CanEdit(int UserID, string SecurityItem)
        {
            return (GetAccessLevel(UserID, SecurityItem) >= 3);
        }
        // 
        public bool CanDelete(int UserID, string SecurityItem)
        {
            return (GetAccessLevel(UserID, SecurityItem) >= 4);
        }
        // 
        public bool CanDo(int UserID, string SecurityItem)
        {
            return (GetAccessLevel(UserID, SecurityItem) >= 0);
        }
        public bool CanDo(string SecurityItem)
        {
            //Uses public Property UserID 
            return (GetAccessLevel(UserID, SecurityItem) >= 1);
            //????? for now - change to > 0 
        }
        // 
        public string GetAccessType(int UserID, string SecurityItem)
        {
            string functionReturnValue = null;
            DataSet ds;
            SQL = "exec GetAccessLevel @UserID=" + UserID + ", @SecurityItemCode='" + SecurityItem + "' ";

            SqlCommand comm = new SqlCommand(SQL, conn);
            ds = new DataSet();
            adap.SelectCommand = comm;
            adap.Fill(ds);
            if (ds.Tables[0].Rows.Count > 0)
            {
                //record found 
                functionReturnValue = ds.Tables[0].Rows[0]["AccessType"].ToString();
            }
            else
            {
                functionReturnValue = "None";
                //default 
            }
            return functionReturnValue;
        }

        public int GetAccessLevel(int UserID, string SecurityItem)
        {
            int functionReturnValue = 0;
            DataSet ds;
            try
            {
                SQL = "if not exists (select * from [Security].SecurityItem where Code = '" + SecurityItem + "')insert into [Security].SecurityItem (Code, SecurityItem) values ('" + SecurityItem + "','" + SecurityItem + "') ";
                sqlDB.execSQL(SQL);
            }
            catch
            {
                //in case it was already there 
            }

            //SQL = "exec GetAccessLevel @UserID=" + UserID + ", @SecurityItemCode='" + SecurityItem + "' ";

            SQL = "select top 1 [Level], AccessType from [Security].ViewUserSecurityItems " +
                " where UserID = " + UserID.ToString() +
                " and Code = '" + SecurityItem + "'" +
                " order by [Level] desc";
            SqlCommand comm = new SqlCommand(SQL, conn);
            ds = new DataSet();
            adap.SelectCommand = comm;
            adap.Fill(ds);
            if (ds.Tables[0].Rows.Count > 0)
            {
                //record found 
                functionReturnValue = Convert.ToInt32(ds.Tables[0].Rows[0]["Level"]);
            }
            else
            {
                functionReturnValue = 0;
                //default 
            }
            return functionReturnValue;
        }

        public DataSet GetGroupSecurity(int UserGroupID)
        {
            DataSet functionReturnValue = null;
            SQL = "select * from ViewGroupSecurityItems ";
            if (UserGroupID > 0)
            {
                SQL += " where UserGroupID=" + UserGroupID.ToString();
            }
            SQL += " order by ID, UserGroup ";
            SqlCommand comm = new SqlCommand(SQL, conn);
            functionReturnValue = new DataSet();
            adap.SelectCommand = comm;
            adap.Fill(functionReturnValue);
            return functionReturnValue;
        }

        public DataSet GetUserGroups(bool AnyOption)
        {
            DataSet functionReturnValue = null;
            SQL = "";
            if (AnyOption)
            {
                SQL = "select 0 as ID, 'Any' as UserGroup UNION ";
            }

            SQL += "select * from [Security].[Group] where UserGroup <> 'All Access'";
            SQL += " order by ID, UserGroup ";
            SqlCommand comm = new SqlCommand(SQL, conn);
            functionReturnValue = new DataSet();
            adap.SelectCommand = comm;
            adap.Fill(functionReturnValue);
            return functionReturnValue;
        }

        public DataSet GetSecurityItems()
        {
            DataSet functionReturnValue = null;
            SQL = "select ID, SecurityItem, Code from [Security].SecurityItem ";
            SQL += " order by SecurityItem ";
            SqlCommand comm = new SqlCommand(SQL, conn);

            functionReturnValue = new DataSet();
            adap.SelectCommand = comm;
            adap.Fill(functionReturnValue);
            return functionReturnValue;
        }

        public DataSet GetAccessTypes()
        {
            DataSet functionReturnValue = null;
            SQL = "select ID, AccessType, Level, cast(Level as char(1))+'.'+AccessType as LevelAccessType from Security.AccessType ";
            SQL += " order by LevelAccessType ";
            SqlCommand comm = new SqlCommand(SQL, conn);
            functionReturnValue = new DataSet();
            adap.SelectCommand = comm;
            adap.Fill(functionReturnValue);
            return functionReturnValue;
        }

        public DataSet getUsers(int UserGroupID, string orderBy, bool AnyOption, bool ActiveOnly)
        {
            DataSet functionReturnValue = null;
            SQL = "";
            if (AnyOption)
            {
                SQL = "select 0 as ID, 'Any' as [User Name], ";
                SQL += "'' as LastName,'' as FirstName, null as Email, '' as LoginCode UNION ALL ";
                SQL += "select distinct UserID, [User Name], LastName, FirstName, Email, LoginCode ";
                SQL += "from Security.ViewUserGroupUsers u where 1=1 ";
            }
            else
            {
                SQL += "select distinct ID, [User Name], LastName, FirstName, Email, InactiveDate, LoginCode, Password, DefaultUserGroupID, ReadOnly, 0 as StandardHourlyRate from Security.ViewUserGroupUsers u where 1=1 ";
            }

            if (ActiveOnly)
            {
                SQL += " and u.InactiveDate is null ";
            }
            if (UserGroupID > 0)
            {
                SQL += " and u.UserGroupID = " + UserGroupID;
            }
            if (orderBy == "FirstName")
            {
                SQL += " order by firstname , Lastname";
            }
            else if (orderBy == "")
            {
                SQL += " order by Firstname, LastName ";
            }
            else
                SQL += " order by " + orderBy;


            SqlCommand comm = new SqlCommand(SQL, conn);
            functionReturnValue = new DataSet();
            adap.SelectCommand = comm;
            adap.Fill(functionReturnValue);
            return functionReturnValue;
        }

        //ByVal UserID As Long) As String 
        public string getUsersGroupList()
        {
            string functionReturnValue = null;
            string sql;
            SqlDataReader dr;
            sql = "select ug.userGroup " + " from [Security].[GroupUser] sgu " + " inner join [Security].[Group] ug on sgu.usergroupid = ug.usergroupid " + " where sgu.userid = " + UserID;
            dr = sqlDB.getDR(sql);
            while (dr.Read())
            {
                functionReturnValue = functionReturnValue + dr.GetValue(0) + ",";
            }
            if (functionReturnValue.EndsWith(","))
                functionReturnValue = functionReturnValue.Remove(functionReturnValue.Length - 1);
            dr.Close();
            return functionReturnValue;
        }
        // 
    }

    public class User : StillVoiceCommon.DataTableRow
    {
        public User(int SessionUserID)
        {
            this.initClass(TN5Y.Utilities.DBConnectionString, "[Security].[User]", "0", "ID", false, SessionUserID);
        }
        public User()
        {
            this.initClass(TN5Y.Utilities.DBConnectionString, "[Security].[User]", "ID");
        }

        public new void updateWithUserIDDate()
        {
            base.updateWithUserIDDate();
            this.addUserToDefaultGroup();
        }
        //public void load(int UserID, )
        //{
        //    base.load(UserID); //KeyValue
        //    base.UserID=SessionUserID; //For Audit trail purposes etc
        //}

        public void addUserToDefaultGroup()
        {

            //ensure that the user is a member of their default group 
            //try to find the record for this user and usergroup 
            string sql;
            if (this.Fields["DefaultUserGroupID"] != DBNull.Value)
            {
                sql = "select * from [Security].[GroupUser] where userid = " + this.KeyValue + " and UserGroupID = " + this.Fields["DefaultUserGroupID"];
                SqlDataReader dr;
                dr = sqlDB.getDR(sql);
                if (!dr.Read())
                {
                    //the record was not found, so add it 
                    dr.Close();
                    sql = "insert into [Security].[GroupUser] VALUES (" + this.KeyValue + ", " + this.Fields["DefaultUserGroupID"] + ")";
                    sqlDB.execSQL(sql);
                }
                else
                {
                    dr.Close();
                }
                dr = null;
            }
            
        }
        public void removeFromGroup(long groupID)
        {
            
            string sql;
            sql = "delete from [Security].[GroupUser] where UserID = " + this.KeyValue + " and UserGroupID = " + groupID;
            sqlDB.execSQL(sql);
        }
        public void addToGroup(long groupID)
        {
            // ERROR: Not supported in C#: OnErrorStatement 
            string sql;
            sql = "insert into [Security].[GroupUser] (UserID, UserGroupID) values (" + this.KeyValue + " ," + groupID + ")";
            sqlDB.execSQL(sql);
        }
        public void removeFromCompany(Int32 companyID)
        {
            string sql;
            sql = "delete from UserCompany where UserID = " + this.KeyValue + " and CompanyID = " + companyID;
            sqlDB.execSQL(sql);
        }
        public void addToCompany(Int32 companyID)
        {
            string sql;
            sql = "insert into UserCompany (UserID, CompanyID) values (" + this.KeyValue + " ," + companyID + ")";
            sqlDB.execSQL(sql);
        }
        public void updatePassword(string Password)
        {
            this.Fields["Password"] = Password;
            this.updateWithUserIDDate();
        }
        public void resetPassword()
        {
            this.updatePassword("password");
        }

        internal bool isEndUser()
        {
            return Convert.ToInt32(this.Fields["DefaultUserGroupID"]) == 5;// End User
        }


        //internal Int32 getDefaultEmployeeID()
        //{
        //    Int32 ID;
        //    ID = Convert.ToInt32(SqlHelper.ExecuteScalar(TN5Y.Utilities.DBConnectionString, CommandType.Text,
        //                    "select min(ID) from Person where IDNumber = @IdentityNumber",
        //                    new SqlParameter("IdentityNumber", IdentityNumber)));
        //    base.load(ID);
        //    return this;
        //}

        internal void loadByLoginCode(string LoginCode)
        {
            Users users = new Users();
            Int32 UserID = users.GetUserIDByLoginCode(LoginCode);
            if (UserID == 0)
                throw new Exception("User record not found for Login Code:" + LoginCode);
            else
                this.load(UserID);
        }

        internal void sendPasswordEmail(string ServerName, string ServerPort)
        {
            string EmailText = "Dear " + this.Fields["FirstName"] + ' ' + this.Fields["LastName"] + "\n\n";
            EmailText += "Your login credentials for the TeamTime are as follows:" + "\n\n";
            //EmailText += "http://" + Request.ServerVariables["SERVER_NAME"] + ":" + Request.ServerVariables["SERVER_PORT"] + "/Login.aspx"  + "\n\n" + "\n\n";
            EmailText += "http://" + ServerName;
            if (ServerPort != "80") EmailText += ":" + ServerPort;
            EmailText+= "/TEPIMS/Login.aspx" + "\n\n\n";

            
            EmailText += "Login:" + this.Fields["LoginCode"] + "\n\n";
            EmailText += "Password:" + this.Fields["Password"] + "\n\n";

            Companies companies = new Companies();
            //string fromEmail = companies.GetCompanyEmail(Convert.ToInt32(Session["CompanyID"]));
            string fromEmail = TN5Y.Utilities.getAdminEmail();
             
            IO.SendEmail(ConfigurationManager.AppSettings["AdminEmail"], this.Fields["Email"].ToString(), "Team Time password reminder", EmailText,false);
        }

        public bool CanDo(string SecurityItem)
        {
            Int32 UserID = Convert.ToInt32(this.KeyValue);
            Security sec = new Security(UserID);
            return (sec.GetAccessLevel(UserID, SecurityItem) >= 1);
        }

        internal void loadByPersonID(int personID)
        {
            string sql = "select isnull(ID,0) from  [Security].[User] where  PersonID = @personID";
            Int32 ID = Convert.ToInt32(SqlHelper.ExecuteScalar(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql,
                                        new SqlParameter("personID", personID)));
            if (ID == 0)
            {
               
            }
            else
                this.load(ID);
        }

        internal void loadByName(string personName)
        {
            string sql = "select isnull(ID,0) from  [Security].[User] where  FirstName + ' ' +LastName = @personName";
            Int32 ID = Convert.ToInt32(SqlHelper.ExecuteScalar(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql,
                                        new SqlParameter("personName", personName)));
            if (ID == 0)
            {

            }
            else
                this.load(ID);
        }
    }

    public class Users
    {
        //GetUserIDByPersonID()
        public Int32 GetUserIDByLoginCode(string LoginCode)
        {
            Int32 ret = 0;
            if (LoginCode != "")
            {
                string sql = "select ID from Security.[User] where LoginCode = @LoginCode";
                SqlDataReader dr = SqlHelper.ExecuteReader(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql,
                                        new SqlParameter("LoginCode", LoginCode));
                if (dr.Read())
                {
                    ret = Convert.ToInt32(dr[0]);
                }
                dr.Close();
            }
            return ret;
        }
       public Int32 getPersonIDFromUser(string UserID)
        {
            Int32 ret = 0;
            if (UserID != "")
            {
                string sql = "select PersonID from Security.[User] where ID = @UserID";
                SqlDataReader dr = SqlHelper.ExecuteReader(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql,
                                        new SqlParameter("UserID", UserID));
                if (dr.Read())
                {
                    ret = Convert.ToInt32(dr[0]);
                }
                dr.Close();
            }
            return ret;
        }
       public Int32 getIDFromViewStaffMembers(Int32 PersonID)
       {
           Int32 ret = 0;
           if (PersonID >= 0)
           {
               string sql = "select ID from ViewStaffMembers where PersonID = @PersonID";
               SqlDataReader dr = SqlHelper.ExecuteReader(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql,
                                       new SqlParameter("PersonID", PersonID.ToString()));
               if (dr.Read())
               {
                   ret = Convert.ToInt32(dr[0]);
               }
               dr.Close();
           }
           return ret;
       }



    }
    public class UserGroup : StillVoiceCommon.DataTableRow
    {
        public UserGroup()
        {
            this.initClass(TN5Y.Utilities.DBConnectionString, "[Security].[Group]", "ID");
        }

        public UserGroup(string DBConnString, long SessionUserID)
        {
            this.initClass(DBConnString, "Security.[Group]", "0", "ID", false, Convert.ToInt32(SessionUserID));
        }
        public void addSecurityItem(long ID)
        {

            string sql;
            sql = "insert into [Security].[GroupSecurity] (UserGroupID, SecurityItemID, AccessTypeID) values (" + this.KeyValue + " ," + ID + ",'D')";
            sqlDB.execSQL(sql);
        }
        public void removeSecurityItem(long ID)
        {
            string sql;
            sql = "delete from [Security].[GroupSecurity] where UserGroupID= " + this.KeyValue + " and SecurityItemID = " + ID;
            sqlDB.execSQL(sql);
        }

        internal void loadByName(string groupName)
        {
            if (groupName != null && groupName != "")
            {
                string sql = "select isnull(ID,0) from  [Security].[Group] where  UserGroup = @GroupName";
                Int32 ID = Convert.ToInt32(SqlHelper.ExecuteScalar(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql,
                                            new SqlParameter("GroupName", groupName)));
                if (ID == 0)
                {
                    if (!groupName.EndsWith("s"))
                        loadByName(groupName + "s"); //e.g. Administrator(s)
                }
                else
                    this.load(ID);
            }
        }
    }
}
